package edu.cdbi.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.cdbi.model.instance.Student;

public class StudentManagement {

	public StudentManagement() {

	}

	public Integer insert(String name, String email) {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return null;
		}

		try {
			PreparedStatement statement = connection
					.prepareStatement("INSERT INTO student (name, email) VALUES(?, ?)");
			statement.setString(1, name);
			statement.setString(2, email);
			statement.executeUpdate();
			statement.close();
			
			statement = connection
					.prepareStatement("SELECT @@identity");
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			int number = resultSet.getInt(1);
			statement.close();
			connection.close();
			return number;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public List<Student> getList() {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return null;
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement("SELECT student_id, name, email FROM student");
			ResultSet resultSet = statement.executeQuery();
			List<Student> list = new ArrayList<Student>();
			while (resultSet.next()) {
				Student student = new Student();
				student.setId(resultSet.getInt("student_id"));
				student.setName(resultSet.getString("name"));
				student.setEmail(resultSet.getString("email"));
				list.add(student);
			}
			resultSet.close();
			statement.close();
			connection.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public boolean delete(int id) {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return false;
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement("DELETE FROM student WHERE student_id=" + id);
			statement.executeUpdate();
			statement.close();
			connection.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
}
